<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1" /><title>DerbyTranslator xref</title>
<link type="text/css" rel="stylesheet" href="../../../../../stylesheet.css" />
</head>
<body>
<div id="overview"><a href="../../../../../../../../../../../../../../apidocs/org/sakaiproject/genericdao/springjdbc/translators/DerbyTranslator.html">View Javadoc</a></div><pre>

<a name="1" href="#1">1</a>   <em>/**<em>*</em></em>
<a name="2" href="#2">2</a>   <em> * $Id: DerbyTranslator.html 11 2009-01-29 14:58:33Z azeckoski $</em>
<a name="3" href="#3">3</a>   <em> * $URL: http://genericdao.googlecode.com/svn/maven/site/xref/org/sakaiproject/genericdao/springjdbc/translators/DerbyTranslator.html $</em>
<a name="4" href="#4">4</a>   <em> * DerbyTranslator.java - genericdao - Apr 26, 2008 2:38:44 PM - azeckoski</em>
<a name="5" href="#5">5</a>   <em> **************************************************************************</em>
<a name="6" href="#6">6</a>   <em> * Copyright (c) 2008 Aaron Zeckoski</em>
<a name="7" href="#7">7</a>   <em> * Licensed under the Apache License, Version 2</em>
<a name="8" href="#8">8</a>   <em> * </em>
<a name="9" href="#9">9</a>   <em> * A copy of the Apache License, Version 2 has been included in this </em>
<a name="10" href="#10">10</a>  <em> * distribution and is available at: <a href="http://www.apache.org/licenses/LICENSE-2.0.txt" target="alexandria_uri">http://www.apache.org/licenses/LICENSE-2.0.txt</a></em>
<a name="11" href="#11">11</a>  <em> *</em>
<a name="12" href="#12">12</a>  <em> * Aaron Zeckoski (azeckoski@gmail.com) (aaronz@vt.edu) (aaron@caret.cam.ac.uk)</em>
<a name="13" href="#13">13</a>  <em> */</em>
<a name="14" href="#14">14</a>  
<a name="15" href="#15">15</a>  <strong>package</strong> org.sakaiproject.genericdao.springjdbc.translators;
<a name="16" href="#16">16</a>  
<a name="17" href="#17">17</a>  <em>/**<em>*</em></em>
<a name="18" href="#18">18</a>  <em> * Apache Derby database translator</em>
<a name="19" href="#19">19</a>  <em> * </em>
<a name="20" href="#20">20</a>  <em> * @author Aaron Zeckoski (azeckoski@gmail.com)</em>
<a name="21" href="#21">21</a>  <em> */</em>
<a name="22" href="#22">22</a>  <strong>public</strong> <strong>class</strong> <a href="../../../../../org/sakaiproject/genericdao/springjdbc/translators/DerbyTranslator.html">DerbyTranslator</a> <strong>extends</strong> <a href="../../../../../org/sakaiproject/genericdao/springjdbc/translators/BasicTranslator.html">BasicTranslator</a> {
<a name="23" href="#23">23</a>  
<a name="24" href="#24">24</a>      <strong>public</strong> String handlesDB() {
<a name="25" href="#25">25</a>          <strong>return</strong> DBTYPE_DERBY;
<a name="26" href="#26">26</a>      }
<a name="27" href="#27">27</a>  
<a name="28" href="#28">28</a>      <strong>public</strong> String makeAutoIdQuery(String tableName, String idColumnName) {
<a name="29" href="#29">29</a>          <strong>return</strong> <span class="string">"values IDENTITY_VAL_LOCAL()"</span>;
<a name="30" href="#30">30</a>      }
<a name="31" href="#31">31</a>  
<a name="32" href="#32">32</a>      <strong>public</strong> String makeLimitQuery(String sql, <strong>long</strong> start, <strong>long</strong> limit, String tableName) {
<a name="33" href="#33">33</a>          <em class="comment">// no real limit support in derby...</em>
<a name="34" href="#34">34</a>          <strong>return</strong> sql;
<a name="35" href="#35">35</a>          <em class="comment">/*</em>
<a name="36" href="#36">36</a>  <em class="comment">         * 5.2. Does Derby support a LIMIT command?</em>
<a name="37" href="#37">37</a>  <em class="comment">Derby supports limiting the number of rows returned by a query through JDBC. For example, to fetch the first 5 rows of a large table:</em>
<a name="38" href="#38">38</a>  
<a name="39" href="#39">39</a>  <em class="comment">Statement stmt = con.createStatement();</em>
<a name="40" href="#40">40</a>  <em class="comment">stmt.setMaxRows(5);</em>
<a name="41" href="#41">41</a>  <em class="comment">ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");</em>
<a name="42" href="#42">42</a>  
<a name="43" href="#43">43</a>  <em class="comment">Some related tuning tips are available in this external article.</em>
<a name="44" href="#44">44</a>  
<a name="45" href="#45">45</a>  <em class="comment">Starting with the 10.4.1.3 release Derby also supports limiting the number of rows using the ROW_NUMBER function.</em>
<a name="46" href="#46">46</a>  
<a name="47" href="#47">47</a>  <em class="comment">For example, to fetch the first 5 rows of a large table:</em>
<a name="48" href="#48">48</a>  
<a name="49" href="#49">49</a>  <em class="comment">SELECT * FROM (</em>
<a name="50" href="#50">50</a>  <em class="comment">    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</em>
<a name="51" href="#51">51</a>  <em class="comment">    FROM myLargeTable</em>
<a name="52" href="#52">52</a>  <em class="comment">) AS tmp</em>
<a name="53" href="#53">53</a>  <em class="comment">WHERE rownum &lt;= 5;</em>
<a name="54" href="#54">54</a>  
<a name="55" href="#55">55</a>  <em class="comment">The ROW_NUMBER function can also be used to select a limited number of rows starting with an offset, for example:</em>
<a name="56" href="#56">56</a>  
<a name="57" href="#57">57</a>  <em class="comment">SELECT * FROM (</em>
<a name="58" href="#58">58</a>  <em class="comment">    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</em>
<a name="59" href="#59">59</a>  <em class="comment">    FROM myLargeTable</em>
<a name="60" href="#60">60</a>  <em class="comment">) AS tmp</em>
<a name="61" href="#61">61</a>  <em class="comment">WHERE rownum > 200000 AND rownum &lt;= 200005;</em>
<a name="62" href="#62">62</a>  
<a name="63" href="#63">63</a>  <em class="comment">For more information, refer to the ROW_NUMBER built-in function in the Derby Reference Manual (available from the Documentation page). Development notes are available on the OLAPRowNumber wiki page.</em>
<a name="64" href="#64">64</a>  
<a name="65" href="#65">65</a>  <em class="comment">The LIMIT keyword is not defined in the SQL standard, and is currently not supported.</em>
<a name="66" href="#66">66</a>  
<a name="67" href="#67">67</a>  <em class="comment">Testing support which would not work:</em>
<a name="68" href="#68">68</a>  <em class="comment">create table testing (ID INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, </em>
<a name="69" href="#69">69</a>  <em class="comment">TITLE VARCHAR(255), SOMETHING VARCHAR(255));</em>
<a name="70" href="#70">70</a>  
<a name="71" href="#71">71</a>  <em class="comment">insert into testing (title) values ('aaronz');</em>
<a name="72" href="#72">72</a>  <em class="comment">insert into testing (title) values ('becky');</em>
<a name="73" href="#73">73</a>  <em class="comment">insert into testing (title) values ('kitty');</em>
<a name="74" href="#74">74</a>  <em class="comment">insert into testing (title) values ('sally');</em>
<a name="75" href="#75">75</a>  <em class="comment">insert into testing (title) values ('billy');</em>
<a name="76" href="#76">76</a>  
<a name="77" href="#77">77</a>  <em class="comment">SELECT * FROM testing WHERE title is not null order by title</em>
<a name="78" href="#78">78</a>  
<a name="79" href="#79">79</a>  <em class="comment">SELECT * FROM ( </em>
<a name="80" href="#80">80</a>  <em class="comment">SELECT ROW_NUMBER() OVER () as rownum, testing.* FROM testing WHERE title is not null ORDER BY title</em>
<a name="81" href="#81">81</a>  <em class="comment">) AS tmp WHERE rownum >= 2 and rownum &lt; 4;</em>
<a name="82" href="#82">82</a>  <em class="comment">         */</em>
<a name="83" href="#83">83</a>      }
<a name="84" href="#84">84</a>  
<a name="85" href="#85">85</a>  }
</pre>
<hr/><div id="footer">This page was automatically generated by <a href="http://maven.apache.org/">Maven</a></div></body>
</html>

